Imports System.IO
Imports System.Data.SqlClient
Imports System.Text
Public Class f_ScriptDirSelection
    Dim DirTree As New Cls_CompDirTree
    Dim objhdlr As New Cls_DCTDevHdlr
    Dim strConString As String = "data source=.;initial catalog=" & f_DCTDevPreparation.f & ";persist security info=False;user id=realm;password=friend;packet size=4096"
    Private Sub btngenscript_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngenscript.Click
        Try
            importscript()
            stagingtablescript()
            Livedbtablescript()
            Createtriggerscript()
            stagingscript()
            PreImpScript()
            Consolidationtablescript()
            MessageBox.Show("Script Generated Successfully")
        Catch ex As Exception
            MessageBox.Show("Please select folder to generate script files")
        End Try
    End Sub
    Private Sub f_ScriptDirSelection_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim drives As String()
        Dim counter As Integer
        drives = Environment.GetLogicalDrives()
        ddlDrivelist.Items.Add("--select--")
        For counter = 0 To drives.Length - 1
            ddlDrivelist.Items.Add(drives(counter).ToString())
        Next
        ddlDrivelist.SelectedIndex = 0
    End Sub
    Private Sub ddlDrivelist_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDrivelist.SelectedIndexChanged
        DirTree.Size = New Size(209, 190)
        DirTree.Location = New Point(12, 173)
        Me.Controls.Clear()
        If ddlDrivelist.SelectedIndex <> 0 Then
            DirTree.Drive = ddlDrivelist.SelectedItem
            Me.Controls.Add(ddlDrivelist)
            Me.Controls.Add(DirTree)
        End If
        Me.Controls.Add(btngenscript)
        Me.Controls.Add(ddlDrivelist)
        Me.Controls.Add(lblScriptpath)
    End Sub
    Private Sub importscript()
        Dim strImp As String
        Dim dsImp As New DataSet
        Dim dtImp As New DataTable
        Dim drImp As System.Data.DataRow
        objhdlr.InsertImportScripts(strConString)
        'Import procedure creation script
        dsImp = objhdlr.GetImportScripts(strConString)
        strImp = DirTree.SelectedNode.FullPath
        strImp = strImp + "\Import Procedure Creation.sql"
        Dim fsImp As New FileStream(strImp, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim sImp As New StreamWriter(fsImp)
        'creating a new StreamWriter and passing the filestream object fs as argument
        sImp.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dtImp = dsImp.Tables(0)
        sImp.WriteLine("IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'sp_ImportBranchTransactions')")
        sImp.WriteLine("DROP PROC sp_ImportBranchTransactions")
        sImp.WriteLine("GO")
        sImp.WriteLine("CREATE PROCEDURE sp_ImportBranchTransactions(@FromDate datetime,@ToDate datetime,@MdbFilePath Varchar(200),@OurBranchID varchar(4))AS BEGIN")
        sImp.WriteLine("DECLARE @SqlInsert varchar(8000), @SqlUpdate varchar(8000),@SqlDelete varchar(8000),@SqlDeleteInsert varchar(8000),@ColumnName varchar(8000),@UpdateCol	Varchar(8000)")
        For Each drImp In dtImp.Rows
            sImp.WriteLine(drImp.Item("Impquery"))
        Next
        sImp.WriteLine("END")
        sImp.WriteLine("GO")
        sImp.Close()
    End Sub
    Private Sub stagingtablescript()
        'Table creation script
        Dim str As String
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim dr As System.Data.DataRow
        ds = objhdlr.GettableList(strConString)
        str = DirTree.SelectedNode.FullPath
        str = str + "\Staging Table Creation.sql"
        Dim fs As New FileStream(str, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim s As New StreamWriter(fs)
        'creating a new StreamWriter and passing the filestream object fs as argument
        s.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dt = ds.Tables(0)
        For Each dr In dt.Rows
            s.WriteLine(dr.Item("tablewisescript"))
            s.WriteLine("GO")
        Next
        s.Close()
    End Sub
    Private Sub Consolidationtablescript()
        'Table creation script
        Dim str As String
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim dr As System.Data.DataRow
        ds = objhdlr.GettableListHO(strConString)
        str = DirTree.SelectedNode.FullPath
        str = str + "\Consolidation Table Creation.sql"
        Dim fs As New FileStream(str, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim s As New StreamWriter(fs)
        'creating a new StreamWriter and passing the filestream object fs as argument
        s.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dt = ds.Tables(0)
        For Each dr In dt.Rows
            s.WriteLine(dr.Item("tablewisescript"))
            s.WriteLine("GO")
        Next
        s.Close()
    End Sub
    Private Sub Livedbtablescript()
        'Table creation script
        Dim strlivetbl As String
        Dim dslivetbl As New DataSet
        Dim dtlivetbl As New DataTable
        Dim drlivetbl As System.Data.DataRow
        dslivetbl = objhdlr.GetLivetableList(strConString)
        strlivetbl = DirTree.SelectedNode.FullPath
        strlivetbl = strlivetbl + "\Live DB Table Creation.sql"
        Dim fslivetbl As New FileStream(strlivetbl, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim slivetbl As New StreamWriter(fslivetbl)
        'creating a new StreamWriter and passing the filestream object fs as argument
        slivetbl.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dtlivetbl = dslivetbl.Tables(0)
        For Each drlivetbl In dtlivetbl.Rows
            slivetbl.WriteLine(drlivetbl.Item("tablewisescript"))
            slivetbl.WriteLine("GO")
        Next
        slivetbl.Close()
        executelivedbtablescript(strlivetbl)
    End Sub
    Private Sub executelivedbtablescript(ByVal strpath As String)
        Dim strcontents As String
        strcontents = ""
        Dim strtemp As String = ""
        Dim objcmd As New SqlCommand
        Dim objreader As StreamReader
        Dim objcon As New SqlConnection(strConString)
        objcmd.Connection = objcon
        objcon.Open()
        objreader = New StreamReader(strpath)
        objcmd.CommandTimeout = 700
        strtemp = objreader.ReadLine
        Do
            If strtemp <> "GO" Then
                strcontents = strcontents + ControlChars.CrLf + strtemp
            Else
                objcmd.CommandText = strcontents.Trim
                objcmd.ExecuteNonQuery()
                strcontents = ""
            End If
            strtemp = objreader.ReadLine
        Loop While Not strtemp Is Nothing
        objreader.Close()
    End Sub
    Private Sub Createtriggerscript()
        'Table creation script
        Dim strtrgr As String
        Dim dstrgr As New DataSet
        Dim dttrgr As New DataTable
        Dim drtrgr As System.Data.DataRow
        dstrgr = objhdlr.GetTriggerScriptList(strConString)
        strtrgr = DirTree.SelectedNode.FullPath
        strtrgr = strtrgr + "\Delete Trigger Creation.sql"
        Dim fstrgr As New FileStream(strtrgr, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim strgr As New StreamWriter(fstrgr)
        'creating a new StreamWriter and passing the filestream object fs as argument
        strgr.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dttrgr = dstrgr.Tables(0)
        For Each drtrgr In dttrgr.Rows
            strgr.WriteLine(drtrgr.Item("trgScript"))
            strgr.WriteLine("GO")
        Next
        strgr.Close()
    End Sub
    Private Sub stagingscript()
        Dim strImp As String
        Dim dsImp As New DataSet
        Dim dtImp As New DataTable
        Dim drImp As System.Data.DataRow
        objhdlr.InsertStagingScripts(strConString)
        'Import procedure creation script
        dsImp = objhdlr.GetStagingScripts(strConString)
        strImp = DirTree.SelectedNode.FullPath
        strImp = strImp + "\Staging Procedure Creation.sql"
        Dim fsImp As New FileStream(strImp, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim sImp As New StreamWriter(fsImp)
        'creating a new StreamWriter and passing the filestream object fs as argument
        sImp.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dtImp = dsImp.Tables(0)
        sImp.WriteLine("IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'sp_BranchStagingTransactions')")
        sImp.WriteLine("DROP PROC sp_BranchStagingTransactions")
        sImp.WriteLine("GO")
        sImp.WriteLine("CREATE PROCEDURE sp_BranchStagingTransactions(@FromDate datetime,@ToDate datetime,@OurBranchID varchar(20))AS BEGIN")
        For Each drImp In dtImp.Rows
            sImp.WriteLine(drImp.Item("StagingQuery"))
        Next
        sImp.WriteLine("END")
        sImp.WriteLine("GO")
        sImp.Close()
    End Sub
    Private Sub PreImpScript()
        Dim strImp As String
        Dim dsImp As New DataSet
        Dim dtImp As New DataTable
        Dim drImp As System.Data.DataRow
        objhdlr.InsertPreImpScripts(strConString)
        'Import procedure creation script
        dsImp = objhdlr.GetPreImpScripts(strConString)
        strImp = DirTree.SelectedNode.FullPath
        strImp = strImp + "\Previous Import Procedure Creation.sql"
        Dim fsImp As New FileStream(strImp, FileMode.Create, FileAccess.Write)
        'declaring a FileStream and creating a word document file named file with 
        'access mode of writing
        Dim sImp As New StreamWriter(fsImp)
        'creating a new StreamWriter and passing the filestream object fs as argument
        sImp.BaseStream.Seek(0, SeekOrigin.End)
        'the seek method is used to move the cursor to next position to avoid text to be
        'overwritten
        dtImp = dsImp.Tables(0)
        sImp.WriteLine("IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'sp_ImportPreviousData')")
        sImp.WriteLine("DROP PROC sp_ImportPreviousData")
        sImp.WriteLine("GO")
        sImp.WriteLine("CREATE PROCEDURE sp_ImportPreviousData(@OurBranchID	Varchar(4))AS BEGIN")
        For Each drImp In dtImp.Rows
            sImp.WriteLine(drImp.Item("PreImpQuery"))
        Next
        sImp.WriteLine("END")
        sImp.WriteLine("GO")
        sImp.Close()
    End Sub
End Class